﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NFL.Core;
using System.Data.Entity;

namespace NFL.DataAccess
{
    public class DataAccess
    {
        public DataAccess()
        {

        }

        public int GetUserID(string userName)
        {
            return GetUsersFromCache().Where(u => u.UserName == userName).Select(u => u.UserID).First();

            //using (NFLEntities db = new NFLEntities())
            //{
            //    return db.Users.Where(u => u.UserName == userName).Select(u => u.UserID).First();
            //}
        }

        public IList<int?> GetDivisions()
        {
            return GetUsersFromCache().Select(u => u.Division).Distinct().OrderBy(u => u.Value).ToList();

            //using (NFLEntities db = new NFLEntities())
            //{
            //    return db.Users.Select(u => u.Division).Distinct().OrderBy(u => u.Value).ToList();
            //}
        }
        
        public int GetWeekID(DateTime inputDate)
        {
            return GetWeeksFromCache().Where(w => w.WeekEnding >= inputDate.Date).OrderBy(w => w.WeekID).Select(w => w.WeekID).FirstOrDefault();

            //using (NFLEntities db = new NFLEntities())
            //{
            //    return db.Weeks.Where(w => w.WeekEnding >= inputDate.Date).OrderBy(w => w.WeekID).Select(w => w.WeekID).FirstOrDefault();
            //}
        }

        private IList<User> GetUsersFromCache()
        {
            // Use cached user table if available
            List<User> users = CacheItems.GetCachedItem(Enums.CacheItems.Users) as List<User>;

            if (users == null)
            {
                // Load User data into cache
                using (NFLEntities db = new NFLEntities())
                {
                    CacheItems.SetCachedItem(Enums.CacheItems.Users, db.Users.ToList());
                    users = db.Users.ToList();
                }

            }

            return users;        
        }

        private IList<Team> GetTeamsFromCache()
        {
            List<Team> teams = CacheItems.GetCachedItem(Enums.CacheItems.Teams) as List<Team>;

            if (teams == null)
            {
                using (NFLEntities db = new NFLEntities())
                {
                    CacheItems.SetCachedItem(Enums.CacheItems.Teams, db.Teams.ToList());
                    teams = db.Teams.ToList();
                }
            }

            return teams;
        }


        private IList<Week> GetWeeksFromCache()
        {
            //DbSet<Week> weeks = CacheItems.GetCachedItem(Enums.CacheItems.Weeks) as DbSet<Week>;
            List<Week> weeks = CacheItems.GetCachedItem(Enums.CacheItems.Weeks) as List<Week>;

            if (weeks == null)
            {
                using (NFLEntities db = new NFLEntities())
                {
                    CacheItems.SetCachedItem(Enums.CacheItems.Weeks, db.Weeks.ToList());
                    weeks = db.Weeks.ToList();
                }
            }

            return weeks;
        }

        private IList<Game> GetGamesFromCache()
        {
            List<Game> games = CacheItems.GetCachedItem(Enums.CacheItems.Games) as List<Game>;

            if (games == null)
            {
                using (NFLEntities db = new NFLEntities())
                {
                    CacheItems.SetCachedItem(Enums.CacheItems.Games, db.Games.ToList());
                    games = db.Games.ToList();
                }
            }

            return games;
        }


        private IList<Pick> GetPicksFromCache()
        {
            List<Pick> picks = CacheItems.GetCachedItem(Enums.CacheItems.Picks) as List<Pick>;

            if (picks == null)
            {
                using (NFLEntities db = new NFLEntities())
                {
                    CacheItems.SetCachedItem(Enums.CacheItems.Picks, db.Picks.Include("Game").Include("Team").ToList(), 1440);  // Expire in 1 day / 1440 minutes
                    picks = db.Picks.Include("Game").Include("Team").ToList();
                }
            }

            return picks;
        }

        private IList<Score> GetScoresFromCache()
        {
            List<Score> scores = CacheItems.GetCachedItem(Enums.CacheItems.Scores) as List<Score>;

            if (scores == null)
            {
                using (NFLEntities db = new NFLEntities())
                {
                    CacheItems.SetCachedItem(Enums.CacheItems.Scores, db.Scores.ToList(), 2);  // Expire in 2 minutes
                    scores = db.Scores.ToList();
                }
            }

            return scores;
        }



        public int GetTeamID(string teamName)
        {
            //DbSet<Team> teams = CacheItems.GetCachedItem(Enums.CacheItems.Teams) as DbSet<Team>;

            //if (teams == null)
            //{
            //    using (NFLEntities db = new NFLEntities())
            //    {
            //        CacheItems.SetCachedItem(Enums.CacheItems.Teams, db.Teams);
            //        teams = db.Teams;
            //    }
            //}

            return GetTeamsFromCache().Where(t => t.LongName == teamName || t.ShortName == teamName).Select(t => t.TeamID).FirstOrDefault();

            //using (NFLEntities db = new NFLEntities())
            //{
            //    return db.Teams.Where(t => t.LongName == teamName || t.ShortName == teamName).Select(t => t.TeamID).FirstOrDefault();
            //}
        }

        public string GetTeamShortName(string teamName)
        {
            //using (NFLEntities db = new NFLEntities())
            //{
            //    return db.Teams.Where(t => t.LongName == teamName).Select(t => t.ShortName).FirstOrDefault();
            //}

            return GetTeamsFromCache().Where(t => t.LongName == teamName).Select(t => t.ShortName).FirstOrDefault();
        }

        public string GetTeamShortName(int teamID)
        {
            //using (NFLEntities db = new NFLEntities())
            //{
            //    return db.Teams.Where(t => t.TeamID == teamID).Select(t => t.ShortName).FirstOrDefault();
            //}

            return GetTeamsFromCache().Where(t => t.TeamID == teamID).Select(t => t.ShortName).FirstOrDefault();
        }


        public void PersistScore(Score inputScore)
        {
            using (NFLEntities db = new NFLEntities())
            {
                // See if game already exists in the database
                var score = GetScoresFromCache().Where(s => s.WeekID == inputScore.WeekID && s.AwayTeam == inputScore.AwayTeam && s.HomeTeam == inputScore.HomeTeam).FirstOrDefault();
                //var score = db.Scores.Where(s => s.WeekID == inputScore.WeekID && s.AwayTeam == inputScore.AwayTeam && s.HomeTeam == inputScore.HomeTeam).FirstOrDefault();

                if (score != null)
                {
                    if (score.Final == false || (score.Final == true && score.AwayScore + score.HomeScore == 0))
                    {
                        // Update data
                        score.HomeScore = inputScore.HomeScore;
                        score.AwayScore = inputScore.AwayScore;
                        score.Final = inputScore.Final;
                        score.TimeStats = inputScore.TimeStats;
                        
                        // Persist to DB
                        db.Entry(score).State = EntityState.Modified;
                        db.SaveChanges();
                    }
                }
                else
                {
                    // Add Row
                    Score newScore = new Score();
                    newScore.WeekID = inputScore.WeekID;
                    newScore.AwayTeam = inputScore.AwayTeam;
                    newScore.AwayScore = inputScore.AwayScore;
                    newScore.HomeTeam = inputScore.HomeTeam;
                    newScore.HomeScore = inputScore.HomeScore;
                    newScore.Final = inputScore.Final;
                    newScore.TimeStats = inputScore.TimeStats;
                    db.Scores.Add(newScore);

                    // Persist to DB
                    db.SaveChanges();
                }
            }
        
        }

        public void PersistGame(Game game)
        {
            using (NFLEntities db = new NFLEntities())
            {
                db.Games.Add(game);
                db.SaveChanges();
            }
        }

        public void PersistPick(Pick pick)
        {
            using (NFLEntities db = new NFLEntities())
            {
                db.Picks.Add(pick);
                db.SaveChanges();
            }
        }

        public IList<Game> GetWeekGames(int weekID)
        {
            return GetGamesFromCache().Where(g => g.WeekID == weekID).OrderBy(g => g.SortOrder).ToList();
            
            //using (NFLEntities db = new NFLEntities())
            //{
            //    return db.Games.Where(g => g.WeekID == weekID).OrderBy(g => g.SortOrder).ToList();
            //}
        }

        public IList<Score> GetCompletedGameScores(int weekID)
        {
            return GetScoresFromCache().Where(s => s.WeekID == weekID && s.Final == true).ToList();

            //using (NFLEntities db = new NFLEntities())
            //{
            //    //return db.Scores.Where(s => s.WeekID == weekID).ToList();
            //    return db.Scores.Where(s => s.WeekID == weekID && s.Final == true).ToList();
            //}
        
        }

        public IList<Score> GetGameScores(int weekID)
        {
            return GetScoresFromCache().Where(s => s.WeekID == weekID).ToList();

            //using (NFLEntities db = new NFLEntities())
            //{
            //    return db.Scores.Where(s => s.WeekID == weekID).ToList();
            //}

        }

        public bool IsWeekOver(int weekID)
        {
            var endGame = GetGamesFromCache().Where(g => g.WeekID == weekID).OrderByDescending(g => g.GameID).FirstOrDefault();
            return (bool)GetScoresFromCache().Where(s => s.WeekID == endGame.WeekID && s.HomeTeam == endGame.HomeTeam && s.AwayTeam == endGame.AwayTeam).Select(s => s.Final).FirstOrDefault();

            //using (NFLEntities db = new NFLEntities())
            //{
            //    //var endGame = db.Games.Where(g => g.WeekID == weekID).OrderByDescending(g => g.GameID).FirstOrDefault();
            //    var endGame = GetGamesFromCache().Where(g => g.WeekID == weekID).OrderByDescending(g => g.GameID).FirstOrDefault();
            //    return (bool)db.Scores.Where(s => s.WeekID == endGame.WeekID && s.HomeTeam == endGame.HomeTeam && s.AwayTeam == endGame.AwayTeam).Select(s => s.Final).FirstOrDefault();
            //}
        }

        public int GetFinalGameTotalScore(int weekID)
        {
            return (int)GetScoresFromCache().Where(s => s.WeekID == weekID && s.Final == true).OrderByDescending(s => s.ScoreID).Select(s => s.HomeScore + s.AwayScore).First();

            //using (NFLEntities db = new NFLEntities())
            //{
            //    return (int)db.Scores.Where(s => s.WeekID == weekID && s.Final == true).OrderByDescending(s => s.ScoreID).Select(s => s.HomeScore + s.AwayScore).First();
            //}
        }

        public IList<User> GetUsers(int division)
        {
            return GetUsersFromCache().Where(u => u.Division == division).OrderBy(u => u.UserID).ToList();

            //using (NFLEntities db = new NFLEntities())
            //{
            //    return db.Users.Where(u => u.Division == division).OrderBy(u => u.UserID).ToList();
            //}
        }

        public IList<Pick> GetPicksForUser(int userID, int weekID)
        {
            return GetPicksFromCache().Where(p => p.UserID == userID && p.Game.WeekID == weekID).OrderBy(p => p.GameID).ToList();

            //using (NFLEntities db = new NFLEntities())
            //{
            //    return db.Picks.Include("Game").Include("Team").Where(p => p.UserID == userID && p.Game.WeekID == weekID).OrderBy(p => p.GameID).ToList();
            //    //return db.Picks.Include("Game").Include("Team").Where(p => p.UserID == userID && p.Game.WeekID == weekID).OrderBy(p => p.Game.SortOrder).ToList();
            //}
        }

        public int GetGameID(int weekID, int sortOrder)
        {
            return GetGamesFromCache().Where(g => g.WeekID == weekID && g.SortOrder == sortOrder).Select(g => g.GameID).FirstOrDefault();

            //using (NFLEntities db = new NFLEntities())
            //{
            //    return db.Games.Where(g => g.WeekID == weekID && g.SortOrder == sortOrder).Select(g => g.GameID).FirstOrDefault();
            //}
        }


        public Score GetSpecificScore(int teamID, int weekID)
        {
            Game game = GetGamesFromCache().Where(g => g.WeekID == weekID && (g.AwayTeam == teamID || g.HomeTeam == teamID)).FirstOrDefault();
            return GetScoresFromCache().Where(s => s.Final == true && s.WeekID == game.WeekID && s.AwayTeam == game.AwayTeam && s.HomeTeam == game.HomeTeam).FirstOrDefault();

            //using (NFLEntities db = new NFLEntities())
            //{
            //    //Game game = db.Games.Where(g => g.WeekID == weekID && (g.AwayTeam == teamID || g.HomeTeam == teamID)).FirstOrDefault();
            //    Game game = GetGamesFromCache().Where(g => g.WeekID == weekID && (g.AwayTeam == teamID || g.HomeTeam == teamID)).FirstOrDefault();
            //    return db.Scores.Where(s => s.Final == true && s.WeekID == game.WeekID && s.AwayTeam == game.AwayTeam && s.HomeTeam == game.HomeTeam).FirstOrDefault();
            //}
        }


    }
}
